/*
EXEC sp_existing_process_specification_file_setup_procedure 1

 */

ALTER PROCEDURE sp_existing_process_specification_file_setup_procedure ( @spec_id INT )
AS

BEGIN
	DECLARE @processName VARCHAR(50)
	DECLARE @baseProduct_ProductID INT
	DECLARE @baseProductName VARCHAR(50)
	DECLARE @selected INT
	DECLARE @isDefault INT
	DECLARE @part_Number VARCHAR(50)
	DECLARE @processStepID INT
	DECLARE @specFileName VARCHAR(50)
	DECLARE @revisionNumber VARCHAR(50)
	DECLARE @partNumberCursor CURSOR
	DECLARE @flag INT
	DECLARE @VENDOR_ID INT

	DECLARE @RESULTS_TABLE TABLE (
					BaseProductName VARCHAR(50),
					selected INT,
				    Part_Number VARCHAR(50),
					ProcessStepID INT,
					specFileName VARCHAR(50),
					revisionNumber VARCHAR(50),
					isDefault INT,
					VENDOR_ID INT
				)

	SELECT @processName = Process_Type FROM Spec_Table WHERE SpecFile_Id = @spec_id
	
	IF( @processName = 'Test' )
	BEGIN
	    SET @processName = 'FT'    
	END

	SET @partNumberCursor = CURSOR for SELECT P_Parent_Product_ID, Part_Number, P.ProcessStep_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = @processName AND P.P_TYPE IS NULL ORDER BY P_Parent_Product_ID
 
	OPEN @partNumberCursor 
	FETCH NEXT FROM @partNumberCursor into @baseProduct_ProductID, @part_Number, @processStepID
 
  	WHILE @@fetch_status = 0
  	BEGIN
		SET @flag = 1
		SELECT @baseProductName = Part_Number FROM PRODUCT WHERE Product_ID = @baseProduct_ProductID
		IF ( ( SELECT COUNT(*) FROM Product_Spec_table WHERE SpecFile_Id = @spec_id AND ProcessStep_Id = @processStepID ) > 0 )
		BEGIN
			SET @flag = 0
		END
		ELSE
		BEGIN
			SET @flag = 1
		END 
		
		IF ( @flag = 0 ) --product selected
		BEGIN
			INSERT INTO @RESULTS_TABLE (  BaseProductName, selected, Part_Number, ProcessStepID, specFileName, revisionNumber, isDefault, VENDOR_ID )			
					SELECT @baseProductName, @flag, @part_Number, @processStepID, Spec_File_Name, Revision, ISNULL( Is_Default, 1 ), VENDOR_ID
						FROM Spec_Table, Product_Spec_table
						WHERE Spec_Table.SpecFile_Id = Product_Spec_table.SpecFile_Id 
						AND Spec_Table.SpecFile_Id = @spec_id  
						AND Product_Spec_table.PROCESSSTEP_ID = @processStepID
		END
		ELSE
		BEGIN
			INSERT INTO @RESULTS_TABLE (  BaseProductName, selected, Part_Number, ProcessStepID, specFileName, revisionNumber, isDefault, VENDOR_ID ) VALUES ( @baseProductName, @flag, @part_Number, @processStepID, NULL, NULL, 1, -1 )
		END

		SET @specFileName 	    = NULL
		SET @revisionNumber 	= NULL
		SET @isDefault			= NULL
		SET @VENDOR_ID			= -1
		FETCH NEXT FROM @partNumberCursor into @baseProduct_ProductID, @part_Number, @processStepID
	END

  	CLOSE @partNumberCursor
  	DEALLOCATE @partNumberCursor

	--DELETING THE PART NUMBERS WHICH HAVE BEEN ASSIGNED TO OTHER SPEC FILE
	--DELETE FROM @RESULTS_TABLE WHERE ProcessStepID IN ( SELECT ProcessStep_Id FROM Product_Spec_table WHERE SpecFile_Id != @spec_id ) AND selected = 1

	SELECT * FROM @RESULTS_TABLE ORDER BY BaseProductName, Part_Number
END

